dbt Seeds 是一個可以把手動維護的 mapping table 加入 dbt 專案/流程的好東西。

case when 或者 mapping table？

這是先前建立的 model: stg_orders

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle_shop', 'orders') }}

訂單狀態（status）所有可能的值如下

• placed
• shipped
• completed
• returned
• return_pending

想像今天要在 stg_orders 新增一個欄位，is_valid (Y/N)

• status = placed, shipped, completed ---> is_valid = Y
• status = returned, return_pending ---> is_valid = N

這樣的需求有幾個處理方式

第一個方法，用 case when

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status,
    case
        when status in ('placed', 'shipped', 'completed')
            then 'Y'
        when status in ('returned', 'return_pending')
            then 'N'
    end as is_valid

from {{ source('jaffle_shop', 'orders') }}

我習慣整理一下，用 CTE 的方式，分兩段做

with source as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from {{ source('jaffle_shop', 'orders') }}
),

transformed as (
    select
        order_id,
        customer_id,
        order_date,
        status,
        case
            when status in ('placed', 'shipped', 'completed')
                then 'Y'
            when status in ('returned', 'return_pending')
                then 'N'
        end as is_valid
    from source
)

select * from transformed

第二個方法，建一個 table，再用 left join 的方式。
這邊的 table 名稱我先留白用 xxxx 代替。

with source as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from {{ source('jaffle_shop', 'orders') }}
),

order_status_mapping as (select * from xxxx),

transformed as (
    select
        t0.order_id,
        t0.customer_id,
        t0.order_date,
        t0.status,
        t1.is_valid
    from source as t0
    left join order_status_mapping as t1 on t0.status = t1.status
)

select * from transformed

當然這邊舉的例子很簡單，實務上用 case when 解決完全沒任何問題。

但如果這個 mapping table 有數十個、數百個值的時候，我們絕對不會想要寫如此冗長的 case when。
這時候還是需要一個 mapping table。

問題來了：這個xxxx的table，要如何把它加入資料庫？後續要如何維護？

過去我待過的 data 團隊，時常需要手動在 SQL database 維護這樣的資料。
create table xxx ...
insert into table xxxx ...
update xxx set xxxx
很麻煩，容易出錯，也沒有版控，難以追溯。

在 dbt 的世界裡，我們則是用 seeds 來維護這類的資料。

用 dbt seed 的做法

新增 seed

在 seed 資料夾，新增檔案 seed_order_statuses.csv，貼入以下內容：

status,is_valid
placed,Y
shipped,Y
completed,Y
returned,N
return_pending,N

指令：dbt seed

在指令列執行 dbt seed，就會將這個 seed 的資料打到我們的目標資料庫。

在 model 使用 dbt seeds

使用方法與 model 類似，用 ref 的語法，就可以引用 seed 的資料。

select * from {{ ref('seed_order_statuses') }}

現在我們就可以利用這個 seed，在 stg_orders 新增欄位。
將前面舉例子的 xxxx 替換成這個 seed 如下。

with source as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from {{ source('jaffle_shop', 'orders') }}
),

order_status_mapping as (select * from {{ ref('seed_order_statuses') }}),

transformed as (
    select
        t0.order_id,
        t0.customer_id,
        t0.order_date,
        t0.status,
        t1.is_valid
    from source as t0
    left join order_status_mapping as t1 on t0.status = t1.status
)

select * from transformed

今日小結＆明日預告

dbt seeds，看起來很簡單的功能，卻幫助我們揮別過去 insert/delete/update 惱人的冗事。
加進 dbt 的流程也代表這些 mapping table 加入了版控及這整個 deployment flow。

提交今天的變更，結束這一天。

明天的主題：dbt Commands 小整理

參考資料

• Add Seeds to your DAG https://docs.getdbt.com/docs/build/seeds
• About dbt seed command https://docs.getdbt.com/reference/commands/seed

歡迎加入 dbt community
對 dbt 或 data 有興趣 👋？歡迎加入 dbt community 到 #local-taipei 找我們，也有實體 Meetup 請到 dbt Taipei Meetup 報名參加